
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/20/2013 23:32:12
-- Generated from EDMX file: C:\Dropbox\Workspace(vs11)\DepartmentOnline\DepartmentOnline\Models\DBModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [aspnet];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_InstructorPublication]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Publications] DROP CONSTRAINT [FK_InstructorPublication];
GO
IF OBJECT_ID(N'[dbo].[FK_InstructorAdvTraining]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AdvTrainings] DROP CONSTRAINT [FK_InstructorAdvTraining];
GO
IF OBJECT_ID(N'[dbo].[FK_InstructorBook]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Books] DROP CONSTRAINT [FK_InstructorBook];
GO
IF OBJECT_ID(N'[dbo].[FK_DisciplineDisciplineRecord]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DisciplineRecords] DROP CONSTRAINT [FK_DisciplineDisciplineRecord];
GO
IF OBJECT_ID(N'[dbo].[FK_GeneralRecordDisciplineRecord]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DisciplineRecords] DROP CONSTRAINT [FK_GeneralRecordDisciplineRecord];
GO
IF OBJECT_ID(N'[dbo].[FK_GroupPlan]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Plans] DROP CONSTRAINT [FK_GroupPlan];
GO
IF OBJECT_ID(N'[dbo].[FK_InstructorDiscipline_Instructor]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstructorDiscipline] DROP CONSTRAINT [FK_InstructorDiscipline_Instructor];
GO
IF OBJECT_ID(N'[dbo].[FK_InstructorDiscipline_Discipline]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[InstructorDiscipline] DROP CONSTRAINT [FK_InstructorDiscipline_Discipline];
GO
IF OBJECT_ID(N'[dbo].[FK_GroupStudent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Students] DROP CONSTRAINT [FK_GroupStudent];
GO
IF OBJECT_ID(N'[dbo].[FK_ClaimStudent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Students] DROP CONSTRAINT [FK_ClaimStudent];
GO
IF OBJECT_ID(N'[dbo].[FK_ClaimInstructor]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Claims] DROP CONSTRAINT [FK_ClaimInstructor];
GO
IF OBJECT_ID(N'[dbo].[FK_ConferenceClaim]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Claims] DROP CONSTRAINT [FK_ConferenceClaim];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Instructors]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Instructors];
GO
IF OBJECT_ID(N'[dbo].[Plans]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Plans];
GO
IF OBJECT_ID(N'[dbo].[Groups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Groups];
GO
IF OBJECT_ID(N'[dbo].[Disciplines]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Disciplines];
GO
IF OBJECT_ID(N'[dbo].[GeneralRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GeneralRecords];
GO
IF OBJECT_ID(N'[dbo].[DisciplineRecords]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DisciplineRecords];
GO
IF OBJECT_ID(N'[dbo].[Publications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Publications];
GO
IF OBJECT_ID(N'[dbo].[AdvTrainings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AdvTrainings];
GO
IF OBJECT_ID(N'[dbo].[Books]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Books];
GO
IF OBJECT_ID(N'[dbo].[Conferences]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Conferences];
GO
IF OBJECT_ID(N'[dbo].[Students]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Students];
GO
IF OBJECT_ID(N'[dbo].[Claims]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Claims];
GO
IF OBJECT_ID(N'[dbo].[InstructorDiscipline]', 'U') IS NOT NULL
    DROP TABLE [dbo].[InstructorDiscipline];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Instructors'
CREATE TABLE [dbo].[Instructors] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Fio] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NULL,
    [Description] nvarchar(max)  NULL,
    [Photo] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Plans'
CREATE TABLE [dbo].[Plans] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Date] datetime  NULL,
    [File] nvarchar(max)  NULL,
    [Group_Id] int  NULL
);
GO

-- Creating table 'Groups'
CREATE TABLE [dbo].[Groups] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Course] nvarchar(max)  NULL,
    [Number] nvarchar(max)  NULL,
    [StudentsQty] int  NOT NULL
);
GO

-- Creating table 'Disciplines'
CREATE TABLE [dbo].[Disciplines] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [WorkingProgramFile] nvarchar(max)  NULL,
    [ScheduleFile] nvarchar(max)  NULL,
    [TermsQty] int  NOT NULL
);
GO

-- Creating table 'GeneralRecords'
CREATE TABLE [dbo].[GeneralRecords] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(max)  NULL,
    [Position] nvarchar(max)  NULL,
    [Parent] nvarchar(max)  NULL,
    [Model] nvarchar(max)  NULL
);
GO

-- Creating table 'DisciplineRecords'
CREATE TABLE [dbo].[DisciplineRecords] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Value] nvarchar(max)  NULL,
    [Parent] nvarchar(max)  NULL,
    [Position] nvarchar(max)  NULL,
    [Discipline_Id] int  NOT NULL,
    [GeneralRecord_Id] int  NOT NULL
);
GO

-- Creating table 'Publications'
CREATE TABLE [dbo].[Publications] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Date] datetime  NULL,
    [Instructor_Id] int  NOT NULL
);
GO

-- Creating table 'AdvTrainings'
CREATE TABLE [dbo].[AdvTrainings] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Date] datetime  NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Instructor_Id] int  NOT NULL
);
GO

-- Creating table 'Books'
CREATE TABLE [dbo].[Books] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Date] datetime  NULL,
    [Instructor_Id] int  NOT NULL
);
GO

-- Creating table 'Conferences'
CREATE TABLE [dbo].[Conferences] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Fio] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NULL,
    [Group_Id] int  NOT NULL,
    [Claim_Id] int  NOT NULL
);
GO

-- Creating table 'Claims'
CREATE TABLE [dbo].[Claims] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Topic] nvarchar(max)  NOT NULL,
    [Result] nvarchar(max)  NULL,
    [Instructor_Id] int  NULL,
    [Conference_Id] int  NOT NULL
);
GO

-- Creating table 'CourseWorks'
CREATE TABLE [dbo].[CourseWorks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Topic] nvarchar(max)  NOT NULL,
    [Date] datetime  NULL,
    [Description] nvarchar(max)  NULL,
    [Instructor_Id] int  NOT NULL
);
GO

-- Creating table 'Tasks'
CREATE TABLE [dbo].[Tasks] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Topic] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [IsComplete] bit  NOT NULL,
    [Instructor_Id] int  NOT NULL
);
GO

-- Creating table 'InstructorDiscipline'
CREATE TABLE [dbo].[InstructorDiscipline] (
    [Instructors_Id] int  NOT NULL,
    [Disciplines_Id] int  NOT NULL
);
GO

-- Creating table 'StudentCourseWork'
CREATE TABLE [dbo].[StudentCourseWork] (
    [Students_Id] int  NOT NULL,
    [CourseWorks_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Instructors'
ALTER TABLE [dbo].[Instructors]
ADD CONSTRAINT [PK_Instructors]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Plans'
ALTER TABLE [dbo].[Plans]
ADD CONSTRAINT [PK_Plans]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Groups'
ALTER TABLE [dbo].[Groups]
ADD CONSTRAINT [PK_Groups]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Disciplines'
ALTER TABLE [dbo].[Disciplines]
ADD CONSTRAINT [PK_Disciplines]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'GeneralRecords'
ALTER TABLE [dbo].[GeneralRecords]
ADD CONSTRAINT [PK_GeneralRecords]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DisciplineRecords'
ALTER TABLE [dbo].[DisciplineRecords]
ADD CONSTRAINT [PK_DisciplineRecords]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Publications'
ALTER TABLE [dbo].[Publications]
ADD CONSTRAINT [PK_Publications]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AdvTrainings'
ALTER TABLE [dbo].[AdvTrainings]
ADD CONSTRAINT [PK_AdvTrainings]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Books'
ALTER TABLE [dbo].[Books]
ADD CONSTRAINT [PK_Books]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Conferences'
ALTER TABLE [dbo].[Conferences]
ADD CONSTRAINT [PK_Conferences]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Claims'
ALTER TABLE [dbo].[Claims]
ADD CONSTRAINT [PK_Claims]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CourseWorks'
ALTER TABLE [dbo].[CourseWorks]
ADD CONSTRAINT [PK_CourseWorks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [PK_Tasks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Instructors_Id], [Disciplines_Id] in table 'InstructorDiscipline'
ALTER TABLE [dbo].[InstructorDiscipline]
ADD CONSTRAINT [PK_InstructorDiscipline]
    PRIMARY KEY NONCLUSTERED ([Instructors_Id], [Disciplines_Id] ASC);
GO

-- Creating primary key on [Students_Id], [CourseWorks_Id] in table 'StudentCourseWork'
ALTER TABLE [dbo].[StudentCourseWork]
ADD CONSTRAINT [PK_StudentCourseWork]
    PRIMARY KEY NONCLUSTERED ([Students_Id], [CourseWorks_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Instructor_Id] in table 'Publications'
ALTER TABLE [dbo].[Publications]
ADD CONSTRAINT [FK_InstructorPublication]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorPublication'
CREATE INDEX [IX_FK_InstructorPublication]
ON [dbo].[Publications]
    ([Instructor_Id]);
GO

-- Creating foreign key on [Instructor_Id] in table 'AdvTrainings'
ALTER TABLE [dbo].[AdvTrainings]
ADD CONSTRAINT [FK_InstructorAdvTraining]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorAdvTraining'
CREATE INDEX [IX_FK_InstructorAdvTraining]
ON [dbo].[AdvTrainings]
    ([Instructor_Id]);
GO

-- Creating foreign key on [Instructor_Id] in table 'Books'
ALTER TABLE [dbo].[Books]
ADD CONSTRAINT [FK_InstructorBook]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorBook'
CREATE INDEX [IX_FK_InstructorBook]
ON [dbo].[Books]
    ([Instructor_Id]);
GO

-- Creating foreign key on [Discipline_Id] in table 'DisciplineRecords'
ALTER TABLE [dbo].[DisciplineRecords]
ADD CONSTRAINT [FK_DisciplineDisciplineRecord]
    FOREIGN KEY ([Discipline_Id])
    REFERENCES [dbo].[Disciplines]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DisciplineDisciplineRecord'
CREATE INDEX [IX_FK_DisciplineDisciplineRecord]
ON [dbo].[DisciplineRecords]
    ([Discipline_Id]);
GO

-- Creating foreign key on [GeneralRecord_Id] in table 'DisciplineRecords'
ALTER TABLE [dbo].[DisciplineRecords]
ADD CONSTRAINT [FK_GeneralRecordDisciplineRecord]
    FOREIGN KEY ([GeneralRecord_Id])
    REFERENCES [dbo].[GeneralRecords]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GeneralRecordDisciplineRecord'
CREATE INDEX [IX_FK_GeneralRecordDisciplineRecord]
ON [dbo].[DisciplineRecords]
    ([GeneralRecord_Id]);
GO

-- Creating foreign key on [Group_Id] in table 'Plans'
ALTER TABLE [dbo].[Plans]
ADD CONSTRAINT [FK_GroupPlan]
    FOREIGN KEY ([Group_Id])
    REFERENCES [dbo].[Groups]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GroupPlan'
CREATE INDEX [IX_FK_GroupPlan]
ON [dbo].[Plans]
    ([Group_Id]);
GO

-- Creating foreign key on [Instructors_Id] in table 'InstructorDiscipline'
ALTER TABLE [dbo].[InstructorDiscipline]
ADD CONSTRAINT [FK_InstructorDiscipline_Instructor]
    FOREIGN KEY ([Instructors_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Disciplines_Id] in table 'InstructorDiscipline'
ALTER TABLE [dbo].[InstructorDiscipline]
ADD CONSTRAINT [FK_InstructorDiscipline_Discipline]
    FOREIGN KEY ([Disciplines_Id])
    REFERENCES [dbo].[Disciplines]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorDiscipline_Discipline'
CREATE INDEX [IX_FK_InstructorDiscipline_Discipline]
ON [dbo].[InstructorDiscipline]
    ([Disciplines_Id]);
GO

-- Creating foreign key on [Group_Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [FK_GroupStudent]
    FOREIGN KEY ([Group_Id])
    REFERENCES [dbo].[Groups]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GroupStudent'
CREATE INDEX [IX_FK_GroupStudent]
ON [dbo].[Students]
    ([Group_Id]);
GO

-- Creating foreign key on [Claim_Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [FK_ClaimStudent]
    FOREIGN KEY ([Claim_Id])
    REFERENCES [dbo].[Claims]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClaimStudent'
CREATE INDEX [IX_FK_ClaimStudent]
ON [dbo].[Students]
    ([Claim_Id]);
GO

-- Creating foreign key on [Instructor_Id] in table 'Claims'
ALTER TABLE [dbo].[Claims]
ADD CONSTRAINT [FK_ClaimInstructor]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClaimInstructor'
CREATE INDEX [IX_FK_ClaimInstructor]
ON [dbo].[Claims]
    ([Instructor_Id]);
GO

-- Creating foreign key on [Conference_Id] in table 'Claims'
ALTER TABLE [dbo].[Claims]
ADD CONSTRAINT [FK_ConferenceClaim]
    FOREIGN KEY ([Conference_Id])
    REFERENCES [dbo].[Conferences]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ConferenceClaim'
CREATE INDEX [IX_FK_ConferenceClaim]
ON [dbo].[Claims]
    ([Conference_Id]);
GO

-- Creating foreign key on [Students_Id] in table 'StudentCourseWork'
ALTER TABLE [dbo].[StudentCourseWork]
ADD CONSTRAINT [FK_StudentCourseWork_Student]
    FOREIGN KEY ([Students_Id])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CourseWorks_Id] in table 'StudentCourseWork'
ALTER TABLE [dbo].[StudentCourseWork]
ADD CONSTRAINT [FK_StudentCourseWork_CourseWork]
    FOREIGN KEY ([CourseWorks_Id])
    REFERENCES [dbo].[CourseWorks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentCourseWork_CourseWork'
CREATE INDEX [IX_FK_StudentCourseWork_CourseWork]
ON [dbo].[StudentCourseWork]
    ([CourseWorks_Id]);
GO

-- Creating foreign key on [Instructor_Id] in table 'CourseWorks'
ALTER TABLE [dbo].[CourseWorks]
ADD CONSTRAINT [FK_InstructorCourseWork]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorCourseWork'
CREATE INDEX [IX_FK_InstructorCourseWork]
ON [dbo].[CourseWorks]
    ([Instructor_Id]);
GO

-- Creating foreign key on [Instructor_Id] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_InstructorTask]
    FOREIGN KEY ([Instructor_Id])
    REFERENCES [dbo].[Instructors]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InstructorTask'
CREATE INDEX [IX_FK_InstructorTask]
ON [dbo].[Tasks]
    ([Instructor_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------